package converter.core.xml;

import com.sun.org.apache.xpath.internal.XPathAPI;
import converter.core.db.DataManager;
import converter.core.db.entity.*;
import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.persistence.NoResultException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.TransformerException;
import java.io.File;
import java.nio.charset.Charset;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * Created with IntelliJ IDEA.
 * User: Snark
 * Date: 28.03.13
 * Time: 22:42
 * To change this template use File | Settings | File Templates.
 */

/**
 * Код конвертера полное гавно.
 * Отнюдь!
 */
public class Converter {

    private Document document;
    private String guidTrainingperiod;

    public Converter(File xmlFile) throws Exception{
        DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
        document = documentBuilder.parse(xmlFile);
    }

    public void convert() throws Exception {
        guidTrainingperiod = updateTrainingperiod();
        updateNullFaculty(guidTrainingperiod);
        String guidFaculty = updateFaculty(guidTrainingperiod);
        String guidWorkcurriculum = updateWorkcurriculum(guidFaculty);
        updateSemester(guidWorkcurriculum);
    }

    private String updateTrainingperiod() throws Exception {
        Node nodeТитул = XPathAPI.selectSingleNode(document, "/Документ/План/Титул");
        Integer valueГодНачалаПодготовки = Integer.parseInt(nodeТитул.getAttributes().getNamedItem(
                "ГодНачалаПодготовки").getNodeValue());

        List result = DataManager.get().em.createNamedQuery("Trainingperiod.findByYears").setParameter(
                "years", valueГодНачалаПодготовки).getResultList();
        if (result == null || result.isEmpty()) {
            Trainingperiod trainingPeriod = new Trainingperiod();
            trainingPeriod.setYears(valueГодНачалаПодготовки);
            trainingPeriod.setSysState(0);
            trainingPeriod.setSysCreated(new Date());
            trainingPeriod.setSysUpdated(new Date());
            trainingPeriod.setSysGuidfk("8C26FC32FE3E43499804321D83E13EA3");
            DataManager.get().persist(trainingPeriod);
        }
        Trainingperiod trainingperiod = (Trainingperiod) DataManager.get().em.createNamedQuery(
                "Trainingperiod.findByYears").setParameter("years", valueГодНачалаПодготовки).getSingleResult();
        return trainingperiod.getSysGuid();
    }

    private void updateNullFaculty(String guidTrainingperiod) {
        String query = "" +
                "SELECT " +
                "   * " +
                "FROM " +
                "   faculty " +
                "WHERE " +
                "   sys_guidfk = ':guidTrainingperiod'" +
                "   AND number = 0";
        query = query.replace(":guidTrainingperiod", guidTrainingperiod);
        List result = DataManager.get().em.createNativeQuery(query).getResultList();
        if (result == null || result.isEmpty()) {
            Faculty faculty = new Faculty();
            faculty.setSysGuidfk(guidTrainingperiod);
            faculty.setNumber(0);
            faculty.setShortname("Общий");
            faculty.setSysState(0);
            faculty.setSysCreated(new Date());
            faculty.setSysUpdated(new Date());
            DataManager.get().persist(faculty);
        }
    }

    private String updateFaculty(String guidTrainingperiod) throws Exception {
        Node nodeТитул = XPathAPI.selectSingleNode(document, "/Документ/План/Титул");
        String valueФакультет = nodeТитул.getAttributes().getNamedItem("Факультет").getNodeValue();

        String query = "SELECT * FROM faculty WHERE shortname = ':shortname' AND sys_guidfk = ':guidTrainingperiod'";
        query = query.replace(":shortname", valueФакультет);
        query = query.replace(":guidTrainingperiod", guidTrainingperiod);

        List result = DataManager.get().em.createNativeQuery(query).getResultList();
        if (result == null || result.isEmpty()) {
            Faculty faculty = new Faculty();
            faculty.setSysGuidfk(guidTrainingperiod);
            faculty.setShortname(valueФакультет);
            faculty.setSysState(0);
            faculty.setSysCreated(new Date());
            faculty.setSysUpdated(new Date());
            DataManager.get().persist(faculty);
        }
        Faculty faculty = (Faculty) DataManager.get().em.createNativeQuery(query, Faculty.class).getSingleResult();
        return faculty.getSysGuid();
    }

    private String updateWorkcurriculum(String guidFaculty) throws Exception {
        Node nodeПлан = XPathAPI.selectSingleNode(document, "/Документ/План");
        String valueФормаОбучения = nodeПлан.getAttributes().getNamedItem("ФормаОбучения").getNodeValue();

        Node nodeТитул = XPathAPI.selectSingleNode(document, "/Документ/План/Титул");
        String valueИмяПлана = nodeТитул.getAttributes().getNamedItem("ИмяПлана").getNodeValue();
        String valueПоследнийШифр = nodeТитул.getAttributes().getNamedItem("ПоследнийШифр").getNodeValue();
        Integer valueВидПлана = Integer.parseInt(nodeТитул.getAttributes().getNamedItem("ВидПлана").getNodeValue());
        Integer valueУровень = Integer.parseInt(nodeТитул.getAttributes().getNamedItem("Уровень").getNodeValue());
        String valueКодУровня = nodeТитул.getAttributes().getNamedItem("КодУровня").getNodeValue();

        String query = "SELECT * FROM workcurriculum WHERE name = ':valueИмяПлана'";
        query = query.replace(":valueИмяПлана", valueИмяПлана);
        List result = DataManager.get().em.createNativeQuery(query).getResultList();
        if (result == null || result.isEmpty()) {
            Workcurriculum workcurriculum = new Workcurriculum();
            workcurriculum.setSysGuidfk(guidFaculty);
            workcurriculum.setName(valueИмяПлана);
            workcurriculum.setCode(valueПоследнийШифр);
            workcurriculum.setLevel(valueУровень);
            workcurriculum.setFormofeducation(valueФормаОбучения);
            workcurriculum.setTypeofplan(valueВидПлана);
            workcurriculum.setCodelevel(valueКодУровня);
            workcurriculum.setSysState(0);
            workcurriculum.setSysCreated(new Date());
            workcurriculum.setSysUpdated(new Date());
            DataManager.get().persist(workcurriculum);
        }
        Workcurriculum workcurriculum = (Workcurriculum) DataManager.get().em.createNativeQuery(query, Workcurriculum.class).getSingleResult();
        return workcurriculum.getSysGuid();
    }

    private void updateSemester(String guidWorkcurriculum) throws Exception {
        Node nodeТитул = XPathAPI.selectSingleNode(document, "/Документ/План");
        String valueФормаОбучения = nodeТитул.getAttributes().getNamedItem("ФормаОбучения").getNodeValue();

        NodeList nodeList = XPathAPI.selectNodeList(document, "/Документ/План/СтрокиПлана/Строка/Сем");
        for (int i = 0; i < nodeList.getLength(); i++) {
            Node node = nodeList.item(i);
            Integer valueНом = Integer.parseInt(node.getAttributes().getNamedItem("Ном").getNodeValue());
            String query = "SELECT * FROM semester WHERE number = :number AND sys_guidfk = ':guidWorkcurriculum'";
            query = query.replace(":number", valueНом.toString());
            query = query.replace(":guidWorkcurriculum", guidWorkcurriculum);
            List result = DataManager.get().em.createNativeQuery(query).getResultList();
            if (result == null || result.isEmpty()) {
                Semester semester = new Semester();
                semester.setNumber(valueНом);
                semester.setSysGuidfk(guidWorkcurriculum);
                semester.setSysState(0);
                semester.setSysCreated(new Date());
                semester.setSysUpdated(new Date());
                DataManager.get().persist(semester);
            }
            Semester semester = (Semester) DataManager.get().em.createNativeQuery(query, Semester.class).getSingleResult();
            updateWcDiscipline(semester.getSysGuid(), node);
        }
    }

    private void updateWcDiscipline(String guidSemester, Node nodeСеместр) {
        Node nodeСтрока = nodeСеместр.getParentNode();
        String valueДис = nodeСтрока.getAttributes().getNamedItem("Дис").getNodeValue();
        if (nodeСтрока.getAttributes().getNamedItem("Кафедра") == null) {
            return;
        }
        Integer valueКафедра = Integer.parseInt(nodeСтрока.getAttributes().getNamedItem("Кафедра").getNodeValue());
        String query0 = "" +
                "SELECT " +
                "   d.* " +
                "FROM " +
                "   discipline as d " +
                "   LEFT JOIN department as dp " +
                "       ON d.sys_guidfk = dp.sys_guid " +
                "   LEFT JOIN faculty as f " +
                "       ON dp.sys_guidfk = f.sys_guid " +
                "   LEFT JOIN trainingperiod as tp " +
                "       ON f.sys_guidfk = tp.sys_guid " +
                "WHERE " +
                "   d.name = ':name'" +
                "   AND tp.sys_guid = ':sysGuid'" +
                "   AND dp.code = :code";
        query0 = query0.replace(":name", valueДис);
        query0 = query0.replace(":sysGuid", guidTrainingperiod);
        query0 = query0.replace(":code", valueКафедра.toString());
        List result = DataManager.get().em.createNativeQuery(query0).getResultList();
        if (result == null || result.isEmpty()) {
            String query1 = "" +
                    "SELECT " +
                    "   dp.* " +
                    "FROM " +
                    "   department as dp" +
                    "   LEFT JOIN faculty as f " +
                    "       ON dp.sys_guidfk = f.sys_guid " +
                    "   LEFT JOIN trainingperiod as tp " +
                    "       ON f.sys_guidfk = tp.sys_guid " +
                    "WHERE " +
                    "   tp.sys_guid = ':sysGuid'" +
                    "   AND dp.code = :code";
            query1 = query1.replace(":sysGuid", guidTrainingperiod);
            query1 = query1.replace(":code", valueКафедра.toString());
            result = DataManager.get().em.createNativeQuery(query1).getResultList();
            if (result == null || result.isEmpty()) {
                String query3 = "" +
                        "SELECT " +
                        "   * " +
                        "FROM " +
                        "   faculty as f " +
                        "   LEFT JOIN trainingperiod as tp " +
                        "       ON f.sys_guidfk = tp.sys_guid " +
                        "WHERE " +
                        "   tp.sys_guid = ':sysGuid' " +
                        "   AND f.number = 0";
                query3 = query3.replace(":sysGuid", guidTrainingperiod);
                Faculty faculty = (Faculty) DataManager.get().em.createNativeQuery(query3, Faculty.class).getSingleResult();

                Department department = new Department();
                department.setCode(valueКафедра);
                department.setSysGuidfk(faculty.getSysGuid());
                department.setSysState(0);
                department.setSysCreated(new Date());
                department.setSysUpdated(new Date());
                DataManager.get().persist(department);
            }
            Department department = (Department) DataManager.get().em.createNativeQuery(query1, Department.class).getSingleResult();

            Discipline discipline = new Discipline();
            discipline.setName(valueДис);
            discipline.setSysGuidfk(department.getSysGuid());
            discipline.setSysState(0);
            discipline.setSysCreated(new Date());
            discipline.setSysUpdated(new Date());
            DataManager.get().persist(discipline);
        }

        Discipline discipline = (Discipline) DataManager.get().em.createNativeQuery(query0, Discipline.class).getSingleResult();

        String query2 = "" +
                "SELECT " +
                "   * " +
                "FROM " +
                "   wc_discipline " +
                "WHERE " +
                "   name = ':disciplineGuid' " +
                "   AND sys_guidfk = ':semesterGuid'";
        query2 = query2.replace(":disciplineGuid", discipline.getSysGuid());
        query2 = query2.replace(":semesterGuid", guidSemester);
        result = DataManager.get().em.createNativeQuery(query2).getResultList();

        Node attrЛек = nodeСеместр.getAttributes().getNamedItem("Лек");
        Node attrЛаб = nodeСеместр.getAttributes().getNamedItem("Лаб");
        Node attrПр = nodeСеместр.getAttributes().getNamedItem("Пр");
        Node attrКСР = nodeСеместр.getAttributes().getNamedItem("КСР");
        Node attrСРС = nodeСеместр.getAttributes().getNamedItem("СРС");
        Node attrИнтЛек = nodeСеместр.getAttributes().getNamedItem("ИнтЛек");
        Node attrИнтЛаб = nodeСеместр.getAttributes().getNamedItem("ИнтЛаб");
        Node attrИнтПр = nodeСеместр.getAttributes().getNamedItem("ИнтПр");
        Node attrЭкз = nodeСеместр.getAttributes().getNamedItem("Экз");
        Node attrЗач = nodeСеместр.getAttributes().getNamedItem("Зач");
        Node attrЗачО = nodeСеместр.getAttributes().getNamedItem("ЗачО");
        Node attrКП = nodeСеместр.getAttributes().getNamedItem("КП");
        Node attrКР = nodeСеместр.getAttributes().getNamedItem("КР");
        Node attrРеф = nodeСеместр.getAttributes().getNamedItem("Реф");
        Node attrЭссе = nodeСеместр.getAttributes().getNamedItem("Эссе");
        Node attrКонтр = nodeСеместр.getAttributes().getNamedItem("Контр");
        Node attrРГР = nodeСеместр.getAttributes().getNamedItem("РГР");

        if (result == null || result.isEmpty()) {
            WcDiscipline wcDiscipline = new WcDiscipline();
            if (attrЛек != null) {
                wcDiscipline.setLecture(Integer.parseInt(attrЛек.getNodeValue()));
            }
            if (attrЛаб != null) {
                wcDiscipline.setLaboratory(Integer.parseInt(attrЛаб.getNodeValue()));
            }
            if (attrПр != null) {
                wcDiscipline.setPractical(Integer.parseInt(attrПр.getNodeValue()));
            }
            if (attrКСР != null) {
                wcDiscipline.setControlsw(Integer.parseInt(attrКСР.getNodeValue()));
            }
            if (attrСРС != null) {
                wcDiscipline.setStudentswork(Integer.parseInt(attrСРС.getNodeValue()));
            }
            if (attrИнтЛек != null) {
                wcDiscipline.setIlecture(Integer.parseInt(attrИнтЛек.getNodeValue()));
            }
            if (attrИнтЛаб != null) {
                wcDiscipline.setIlaboratory(Integer.parseInt(attrИнтЛаб.getNodeValue()));
            }
            if (attrИнтПр != null) {
                wcDiscipline.setIpractical(Integer.parseInt(attrИнтПр.getNodeValue()));
            }
            if (attrЭкз != null) {
                wcDiscipline.setExamination(Integer.parseInt(attrЭкз.getNodeValue()));
            }
            if (attrЗач != null) {
                wcDiscipline.setOffset(Integer.parseInt(attrЗач.getNodeValue()));
            }
            if (attrЗачО != null) {
                wcDiscipline.setOffseto(Integer.parseInt(attrЗачО.getNodeValue()));
            }
            if (attrКП != null) {
                wcDiscipline.setCourseproj(Integer.parseInt(attrКП.getNodeValue()));
            }
            if (attrКР != null) {
                wcDiscipline.setCoursework(Integer.parseInt(attrКР.getNodeValue()));
            }
            if (attrРеф != null) {
                wcDiscipline.setRef(Integer.parseInt(attrРеф.getNodeValue()));
            }
            if (attrЭссе != null) {
                wcDiscipline.setEsse(Integer.parseInt(attrЭссе.getNodeValue()));
            }
            if (attrКонтр != null) {
                wcDiscipline.setTest(Integer.parseInt(attrКонтр.getNodeValue()));
            }
            if (attrРГР != null) {
                wcDiscipline.setRgr(Integer.parseInt(attrРГР.getNodeValue()));
            }
            wcDiscipline.setSysState(0);
            wcDiscipline.setSysCreated(new Date());
            wcDiscipline.setSysUpdated(new Date());
            DataManager.get().persist(wcDiscipline);
        }

    }
}
